<?php
date_default_timezone_set('America/Los_Angeles');
$gstartdate = date('m/d/Y h:i:s a', time());
$table_name = 'PROJECT';
$data_type = "";
$TABLE_LIST = "";
$comma = "";
debug("\r\n".$gstartdate.":".$table_name.".....Start");
processSelect($table_name);
 function validateNumber($field_in)
 {
   $num = $field_in->value;
   $new_num = 0;  
   if(is_numeric ($num))
   {
     $new_num = $num;
   } 
   return $new_num;
 }
   function validateDate($field_in)
 {
       $val = '';
 	   $test_date = $field_in->value;
	   if(preg_match("/^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$/", $test_date ) === 0) 
	   {$test_date = '';}
	   if(strtotime( $test_date) === true) 
	   {
	     $test_date_mon = explode(' ',$test_date);
		 $val = $test_date_mon[0];
	   }
    return $val;
 } 
 function validateVarchar2($field_in)
 {
   return validateClob($field_in);
 } 
  function validateClob($field_in)
 {
    $string = $field_in->value;
    $search = preg_replace('/[àáâãåäæ]/iu', '', $string);
    $search = preg_replace('/[èéêë]/iu', '', $search);
    $search = preg_replace('/[ìíîï]/iu', '', $search);
    $search = preg_replace('/[òóôõöø]/iu', '', $search);
    $search = preg_replace('/[ùúûü]/iu', '', $search);
	$search = preg_replace("/[\r\n]+/", "", $search);
	$search = preg_replace("/[']+/", "''", $search);
    //$my_string = preg_replace('~&([a-z]{1,2})(?:acute|cedil|circ|grave|lig|orn|ring|slash|th|tilde|uml|caron);~i', '$1', htmlentities($string, ENT_COMPAT, 'UTF-8'));
    $my_string = str_replace("<", "", $search);
	$my_string = str_replace(">", "", $my_string);
	$my_string = str_replace("&", "", $my_string);
   return trim($my_string); 
 } 
//---------------------------------------------------------------- 
function debug($v){echo $v."\r\n";}
 
function  processSelect($table_name_in )
{
 $commit_count = 0;
 $my_file = "VFP_".$table_name_in.'.sql';
 unlink( $my_file);
 $handle = fopen($my_file, 'w') or die('Cannot open file:  '.$my_file); 

  $vfp_conn = new COM("ADODB.Connection") or die("Cannot start ADO");
  $vfp_conn->Open("Provider=VFPOLEDB.1;Data Source=C:\\VFPDATA\\DATA\\evrs.dbc;Collating Sequence=machine");
  $sql = "SELECT * FROM " . $table_name_in;
  $rs = $vfp_conn->Execute($sql);  
  $sql_head = createInsertStringHead($rs,$table_name_in);
  $rowcount = 0;
  if(!$rs->EOF){
   
    $t = "truncate table VFP_PROJECT";
    fwrite($handle, $t.";\r\n");
	while (!$rs->EOF) {

	 $rowcount ++;
 
$ctips_id = validateNumber($rs->Fields("ctips_id"));
$projectid = validateNumber($rs->Fields("projectid"));
$districtid = "'".validateVarchar2($rs->Fields("districtid"))."'";
$ea_number = "'".validateVarchar2($rs->Fields("ea_number"))."'";
$ppno = "'".validateVarchar2($rs->Fields("ppno"))."'";
$countyid = "'".validateVarchar2($rs->Fields("countyid"))."'";
$route1 = "'".validateVarchar2($rs->Fields("route1"))."'";
$pm1bf = "'".validateVarchar2($rs->Fields("pm1bf"))."'";
$pm1b = validateNumber($rs->Fields("pm1b"));
$pm1af = "'".validateVarchar2($rs->Fields("pm1af"))."'";
$pm1a = validateNumber($rs->Fields("pm1a"));
$docyear = validateNumber($rs->Fields("docyear"));
$document = "'".validateVarchar2($rs->Fields("document"))."'";
$official = validateNumber($rs->Fields("official"));
$high_ver = "'".validateVarchar2($rs->Fields("high_ver"))."'";
$mpoid    = validateNumber($rs->Fields("mpoid"));
$agencyid = "'".validateVarchar2($rs->Fields("agencyid"))."'";
$version = validateNumber($rs->Fields("version"));
$chg_offcl = validateNumber($rs->Fields("chg_offcl"));
$chg_qual1 = validateNumber($rs->Fields("chg_qual1"));
$chg_qual2 = validateNumber($rs->Fields("chg_qual2"));
//$comnt_anal = validateClob($rs->Fields("comnt_anal"));
///$comnt_dist = validateClob($rs->Fields("comnt_dist"));
//$comnt_mpo = validateClob($rs->Fields("comnt_mpo"));
//$comnt_othr = validateClob($rs->Fields("comnt_othr"));
//$comnt_senr = validateClob($rs->Fields("comnt_senr"));
$corridor = validateNumber($rs->Fields("corridor"));
//$desc = validateClob($rs->Fields("desc"));
$elementid = "'".validateVarchar2($rs->Fields("elementid"))."'";
$lupdate = validateDate($rs->Fields("lupdate"));
$lupdateid = "'".validateVarchar2($rs->Fields("lupdateid"))."'";
$mpo = "'".validateVarchar2($rs->Fields("mpo"))."'";
$postmiles1 = validateNumber($rs->Fields("postmiles1"));
$prjmgr = "'".validateVarchar2($rs->Fields("prjmgr"))."'";
$prjmgrphon = "'".validateVarchar2($rs->Fields("prjmgrphon"))."'";
$title = "'".validateVarchar2($rs->Fields("title"))."'";
$l_vote = "'".validateVarchar2($rs->Fields("l_vote"))."'";
$votedate = validateDate($rs->Fields("votedate"));
$tciprj = "'".validateVarchar2($rs->Fields("tciprj"))."'";
$deny_fed = "'".validateVarchar2($rs->Fields("deny_fed"))."'";
$deny_state = "'".validateVarchar2($rs->Fields("deny_state"))."'";
$cost_incr = "'".validateVarchar2($rs->Fields("cost_incr"))."'";
$scope_incr = "'".validateVarchar2($rs->Fields("scope_incr"))."'";
$cost_decr = "'".validateVarchar2($rs->Fields("cost_decr"))."'";
$scope_decr = "'".validateVarchar2($rs->Fields("scope_decr"))."'";
$sch_delay = "'".validateVarchar2($rs->Fields("sch_delay"))."'";
$sch_adv = "'".validateVarchar2($rs->Fields("sch_adv"))."'";
$fnd_src_ch = "'".validateVarchar2($rs->Fields("fnd_src_ch"))."'";
$compnt_chg = "'".validateVarchar2($rs->Fields("compnt_chg"))."'";
$prjsplit = "'".validateVarchar2($rs->Fields("prjsplit"))."'";
$prjcomb = "'".validateVarchar2($rs->Fields("prjcomb"))."'";
$high_offcl = "'".validateVarchar2($rs->Fields("high_offcl"))."'";
$bookitem = "'".validateVarchar2($rs->Fields("bookitem"))."'";
$l_unpar = "'".validateVarchar2($rs->Fields("l_unpar"))."'";
$l_trminate = "'".validateVarchar2($rs->Fields("l_trminate"))."'";
$archive = "'".validateVarchar2($rs->Fields("archive"))."'";
$implpaed = "'".validateVarchar2($rs->Fields("implpaed"))."'";
$implpse = "'".validateVarchar2($rs->Fields("implpse"))."'";
$implrw = "'".validateVarchar2($rs->Fields("implrw"))."'";
$implcon = "'".validateVarchar2($rs->Fields("implcon"))."'";
$l_caltrans = "'".validateVarchar2($rs->Fields("l_caltrans"))."'";
$countyid2 = "'".validateVarchar2($rs->Fields("countyid2"))."'";
$route2 = "'".validateVarchar2($rs->Fields("route2"))."'";
$pm2bf = "'".validateVarchar2($rs->Fields("pm2bf"))."'";
$pm2b = validateNumber($rs->Fields("pm2b"));
$pm2af = "'".validateVarchar2($rs->Fields("pm2af"))."'";
$pm2a = validateNumber($rs->Fields("pm2a"));
$postmiles2 = validateNumber($rs->Fields("postmiles2"));
$countyid3 = "'".validateVarchar2($rs->Fields("countyid3"))."'";
$route3 = "'".validateVarchar2($rs->Fields("route3"))."'";
$pm3bf = "'".validateVarchar2($rs->Fields("pm3bf"))."'";
$pm3b = validateNumber($rs->Fields("pm3b"));
$pm3af = "'".validateVarchar2($rs->Fields("pm3af"))."'";
$pm3a = validateNumber($rs->Fields("pm3a"));
$postmiles3 = validateNumber($rs->Fields("postmiles3"));
	
	
	
	
$sql = 'INSERT INTO VFP_PROJECT(';
$sql = $sql.'"CTIPS_ID",';
$sql = $sql. '"PROJECTID",';
$sql = $sql. '"DISTRICTID",';
$sql = $sql. '"EA_NUMBER",';
$sql = $sql. '"PPNO",';
$sql = $sql. '"COUNTYID",';
$sql = $sql. '"ROUTE1",';
$sql = $sql. '"PM1BF",';
$sql = $sql. '"PM1B",';
$sql = $sql. '"PM1AF",';
$sql = $sql. '"PM1A",';
$sql = $sql. '"DOCYEAR",';
$sql = $sql. '"DOCUMENT",';
$sql = $sql. '"OFFICIAL",';
$sql = $sql. '"HIGH_VER",';
$sql = $sql. '"MPOID",';
$sql = $sql. '"AGENCYID",';
$sql = $sql. '"VERSION",';
$sql = $sql. '"CHG_OFFCL",';
$sql = $sql. '"CHG_QUAL1",';
$sql = $sql. '"CHG_QUAL2",';
//$sql = $sql. '"COMNT_ANAL",';
//$sql = $sql. '"COMNT_DIST",';
//$sql = $sql. '"COMNT_MPO",';
//$sql = $sql. '"COMNT_OTHR",';
//$sql = $sql. '"COMNT_SENR",';
$sql = $sql. '"CORRIDOR",';
//$sql = $sql. '"DESC",';
$sql = $sql. '"ELEMENTID",';
$sql = $sql. '"LUPDATE",';
$sql = $sql. '"LUPDATEID",';
$sql = $sql. '"MPO",';
$sql = $sql. '"POSTMILES1",';
$sql = $sql. '"PRJMGR",';
$sql = $sql. '"PRJMGRPHON",';
$sql = $sql. '"TITLE",';
$sql = $sql. '"L_VOTE",';
$sql = $sql. '"VOTEDATE",';
$sql = $sql. '"TCIPRJ",';
$sql = $sql. '"DENY_FED",';
$sql = $sql. '"DENY_STATE",';
$sql = $sql. '"COST_INCR",';
$sql = $sql. '"SCOPE_INCR",';
$sql = $sql. '"COST_DECR",';
$sql = $sql. '"SCOPE_DECR",';
$sql = $sql. '"SCH_DELAY",';
$sql = $sql. '"SCH_ADV",';
$sql = $sql. '"FND_SRC_CH",';
$sql = $sql. '"COMPNT_CHG",';
$sql = $sql. '"PRJSPLIT",';
$sql = $sql. '"PRJCOMB",';
$sql = $sql. '"HIGH_OFFCL",';
$sql = $sql. '"BOOKITEM",';
$sql = $sql. '"L_UNPAR",';
$sql = $sql. '"L_TRMINATE",';
$sql = $sql. '"ARCHIVE",';
$sql = $sql. '"IMPLPAED",';
$sql = $sql. '"IMPLPSE",';
$sql = $sql. '"IMPLRW",';
$sql = $sql. '"IMPLCON",';
$sql = $sql. '"L_CALTRANS",';
$sql = $sql. '"COUNTYID2",';
$sql = $sql. '"ROUTE2",';
$sql = $sql. '"PM2BF",';
$sql = $sql. '"PM2B",';
$sql = $sql. '"PM2AF",';
$sql = $sql. '"PM2A",';
$sql = $sql. '"POSTMILES2",';
$sql = $sql. '"COUNTYID3",';
$sql = $sql. '"ROUTE3",';
$sql = $sql. '"PM3BF",';
$sql = $sql. '"PM3B",';
$sql = $sql. '"PM3AF",';
$sql = $sql. '"PM3A",';
$sql = $sql. '"POSTMILES3")VALUES(';
$sql = $sql." $ctips_id,";
$sql = $sql." $projectid,";
$sql = $sql." $districtid,";
$sql = $sql." $ea_number,";
$sql = $sql." $ppno,";
$sql = $sql." $countyid,";
$sql = $sql." $route1,";
$sql = $sql." $pm1bf,";
$sql = $sql." $pm1b,";
$sql = $sql." $pm1af,";
$sql = $sql." $pm1a,";
$sql = $sql." $docyear,";
$sql = $sql." $document,";
$sql = $sql." $official,";
$sql = $sql." $high_ver,";
$sql = $sql." $mpoid,";
$sql = $sql." $agencyid,";
$sql = $sql." $version,"; 
$sql = $sql." $chg_offcl,";
$sql = $sql." $chg_qual1,";
$sql = $sql." $chg_qual2,";
//$sql = $sql." EMPTY_CLOB(),";
//$sql = $sql." EMPTY_CLOB(),";
//$sql = $sql." EMPTY_CLOB(),";
//$sql = $sql." EMPTY_CLOB(),";
//$sql = $sql." EMPTY_CLOB(),";
$sql = $sql." $corridor,";
//$sql = $sql." EMPTY_CLOB(),";
$sql = $sql." $elementid,";
$sql = $sql."'$lupdate',";
$sql = $sql." $lupdateid,";
$sql = $sql." $mpo,";
$sql = $sql." $postmiles1,";
$sql = $sql." $prjmgr,";
$sql = $sql." $prjmgrphon,";
$sql = $sql." $title,";
$sql = $sql." $l_vote,";
$sql = $sql."'$votedate',";
$sql = $sql." $tciprj,";
$sql = $sql." $deny_fed,";
$sql = $sql." $deny_state,";
$sql = $sql." $cost_incr,";
$sql = $sql." $scope_incr,";
$sql = $sql." $cost_decr,";
$sql = $sql." $scope_decr,";
$sql = $sql." $sch_delay,";
$sql = $sql." $sch_adv,";
$sql = $sql." $fnd_src_ch,";
$sql = $sql." $compnt_chg,";
$sql = $sql." $prjsplit,";
$sql = $sql." $prjcomb,";
$sql = $sql." $high_offcl,";
$sql = $sql." $bookitem,";
$sql = $sql." $l_unpar,";
$sql = $sql." $l_trminate,";
$sql = $sql." $archive,";
$sql = $sql." $implpaed,";
$sql = $sql." $implpse,";
$sql = $sql." $implrw,";
$sql = $sql." $implcon,";
$sql = $sql." $l_caltrans,";
$sql = $sql." $countyid2,";
$sql = $sql." $route2,";
$sql = $sql." $pm2bf,";
$sql = $sql." $pm2b,";
$sql = $sql." $pm2af,";
$sql = $sql." $pm2a,";
$sql = $sql." $postmiles2,";
$sql = $sql." $countyid3,";
$sql = $sql." $route3,";
$sql = $sql." $pm3bf,";
$sql = $sql." $pm3b,";
$sql = $sql." $pm3af,";
$sql = $sql." $pm3a,";
$sql = $sql." $postmiles3)";
/*
$sql = $sql." RETURNING "; 
$sql = $sql.'"COMNT_ANAL",';
$sql = $sql.'"COMNT_DIST",';
$sql = $sql.'"COMNT_MPO",';
$sql = $sql.'"COMNT_OTHR",';
$sql = $sql.'"COMNT_SENR",';
$sql = $sql.'"DESC"';
$sql = $sql.' INTO ';
$sql = $sql.":bv_comnt_anal,";
$sql = $sql.":bv_comnt_dist,";
$sql = $sql.":bv_comnt_mpo,";
$sql = $sql.":bv_comnt_othr,";
$sql = $sql.":bv_comnt_senr,";
$sql = $sql.":bv_desc";
	*/
	
 
 
	 fwrite($handle, $sql.";\r\n");
	 if($commit_count > 99)
	 {
	   $commit_count = 0;
	    fwrite($handle, "commit;\r\n");
	 } 
	   $commit_count++;
	         
     $rs->MoveNext();
  
	}
	fwrite($handle, "commit;\r\n");
	try{	  
	  debug("\r\n Count:".$rowcount);
      $rs->Close();
	  $rs = null; 
	  $vfp_conn->Close();
	  fclose($handle);
	  //oci_close ( $oracle_conn );
	  //$oracle_conn = null;
	  $vfp_conn = null;
	 }catch(Exception $e)
	 {
	   echo "\r\n================================================================================";
	   echo "\r\n".$e->getMessage()."\r\n";
	   echo "================================================================================";
	 }
  }
}
function createSqlData($rs_in)
{    $sql = "";
     $comma = "";
     $num_columns = $rs_in->Fields->Count();
     for ($i=0; $i < $num_columns; $i++) {
     $fld = $rs_in->Fields($i);
	
	  $sql = $sql.$comma;
	 switch ( $fld->type) {
    case 129:
        //debug("varchar2");
                $my_string = ""; 
		$my_string = trim(preg_replace('/\s\s+/', ' ', $fld->value));
		$my_string = str_replace("'", "", $my_string);
		$my_string = removeAccents( $my_string);
		
		
		$sql = $sql."'".$my_string."'" ;
        break;
    case 5:
        //debug("NUMBER");//FLOAT
		
		$sql = $sql.checkForNumber($fld->value);
        break;
   case 3:
        //debug("NUMBER");//INT
       $sql = $sql.checkForNumber($fld->value);
		break;
   case 131:
        //debug( "NUMBER");//INT
		$sql = $sql.checkForNumber($fld->value);
        break;		
   case 11:
       //debug("VARCHAR2(5)");//BOOLEAN
	    $my_string = trim(preg_replace('/\s\s+/', ' ', $fld->value));
		$my_string = str_replace("'", "", $my_string);
		$my_string = removeAccents($my_string);
		$my_string = trim($my_string);
        if( is_null($my_string) === 1){$my_string = 'F';}
	    if( empty($my_string) === 1){$my_string = 'F';}
	    if( $my_string === '1'){$my_string = 'T';}
	    if( $my_string === '0'){$my_string = 'F';}
	    if(strlen( $my_string ) === 0 ){$my_string = 'F';}
		$sql = $sql."'".$my_string."'" ;
        break;		
		
   case 201:
        //debug( "VARCHAR2");//BOOLEAN
		$my_string = trim(preg_replace('/\s\s+/', ' ', $fld->value));
		$my_string = str_replace("'", "", $my_string);
		$my_string = removeAccents( $my_string);
		$my_string = trim($my_string);
		if( is_null($my_string) === 1){$my_string = 'F';}
		if( empty($my_string) === 1){$my_string = 'F';}
	    if( $my_string === '1'){$my_string = 'T';}
	    if( $my_string === '0'){$my_string = 'F';}
	    if(strlen( $my_string ) === 0 ){$my_string = 'F';}
	 
		$sql = $sql."'".$my_string."'" ;
        break;		
    case 135:
	try{
	   $test_date = $fld->value;
	   if(preg_match("/^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$/", $test_date ) === 0) 
	   {$test_date = "";}
	   if(strtotime( $test_date) === false) 
	   {
	     $sql = $sql."NULL" ;
	   }
	     else
	   {
	     $test_date_mon = explode(' ',$test_date);
		 $sql = $sql."TO_DATE('".$test_date_mon[0]."','MM/DD/YYYY')" ;
	   }
       // debug("DATE");//DATE
		  }catch(Exception $e)
	{
	  echo $e->getMessage();
	  $sql = $sql."NULL" ;
	}
        break;
    case 133:
	try{
        $test_date = $fld->value;
	    if(preg_match("/^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$/", $test_date ) === 0) 
	    {$test_date = "";}
	    if(strtotime(  $test_date) === false) 
	   {
	     $sql = $sql."NULL";
	   }
	     else
	   {
	     
	     $test_date_mon = explode(' ',$test_date);
	     $sql = $sql."TO_DATE('".$test_date_mon[0]."','MM/DD/YYYY')";
	   }
	   		  }catch(Exception $e)
	{
	  echo $e->getMessage();
	  $sql = $sql."NULL" ;
	}
        break;
	default:
	    debug("\r\n=======================================================\r\n");
		debug("NOT FOUND ".$fld[$i]->type);
		debug("\r\n=======================================================\r\n");
}        
	  
 
	 //$sql = 
	 $comma = ",";
   }
   return $sql.')';
}
function checkForNumber($num)
{
  $new_num = 0;
  try{
  //if(is_nan($num)){echo "found it"."\r\n"; return 0;}
  if(is_numeric ($num))
  {
    $new_num = $num;
  }
  }catch(Exception $a)
  {
      $new_num = 0;
  }
  return $new_num;
}
function removeAccents($string) {

    $search = preg_replace('/[àáâãåäæ]/iu', '', $string);
    $search = preg_replace('/[èéêë]/iu', '', $search);
    $search = preg_replace('/[ìíîï]/iu', '', $search);
    $search = preg_replace('/[òóôõöø]/iu', '', $search);
    $search = preg_replace('/[ùúûü]/iu', '', $search);
	$search = preg_replace("/[\r\n]+/", "", $search);
	$search = preg_replace("/[']+/", "''", $search);
    //$my_string = preg_replace('~&([a-z]{1,2})(?:acute|cedil|circ|grave|lig|orn|ring|slash|th|tilde|uml|caron);~i', '$1', htmlentities($string, ENT_COMPAT, 'UTF-8'));
    $my_string = str_replace("<", "", $search);
	$my_string = str_replace(">", "", $my_string);
	$my_string = str_replace("&", "", $my_string);
 return $my_string; 
}
function createInsertStringHead($rs_in,$table_name_in)
{   
   $comma = "";
   $sql = "INSERT INTO VFP_".strtoupper($table_name_in)."(";
   $num_columns = $rs_in->Fields->Count(); 
   for ($i=0; $i < $num_columns; $i++) {
     $fld[$i] = $rs_in->Fields($i);
	 $fld_name = strtoupper($fld[$i]->name);
	 $sql = $sql.$comma."\"".$fld_name."\"";
	 $comma = ",";
   }
   return $sql.")VALUES(";
}
//-----------------------------------------------------------------
$gstartdate = date('m/d/Y h:i:s a', time());
debug("\r\n".$gstartdate.":".$table_name.".....Done");
?>